const express = require("express")
const router = express.Router()
const { db, genid } = require("../db/DbUtils")
//在这里,我们只需要前端传category_id,title,content这三个值,分类的时候用category_id更好,因为如果传分类的名字可能有重复

//添加博客
router.post("/_token/add", async (req, res) => {
  let { category_id, title, content } = req.body
  let id = genid.NextId()
  let create_time = new Date().getTime()
  const insert_sql = "insert into `blog` (`id`,`category_id`,`title`,`content`,`create_time`) values (?,?,?,?,?)"
  let { err, rows } = await db.async.run(insert_sql, [id, category_id, title, content, create_time])
  if (err == null) {
    res.send({
      code: 200,
      msg: "添加成功"
    })
  } else {
    res.send({
      code: 500,
      msg: "添加失败"
    })
  }
})

//修改博客
router.put("/_token/update", async (req, res) => {
  let { id, category_id, title, content } = req.body
  const update_sql = "update `blog` set `category_id`=?,`title`=?,`content`=? where `id`= ?"
  let { err, rows } = await db.async.run(update_sql, [category_id, title, content, id])
  if (err == null) {
    res.send({
      code: 200,
      msg: "修改成功"
    })
  } else {
    res.send({
      code: 500,
      msg: "修改失败"
    })
  }
})

//删除博客
router.delete("/_token/delete", async (req, res) => {
  let id = req.query.id
  const delete_sql = "delete from `blog` where `id` = ?;"
  let { err, rows } = await db.async.run(delete_sql, [id])
  if (err == null) {
    res.send({
      code: 200,
      msg: "删除成功"
    })
  } else {
    res.send({
      code: 500,
      msg: "删除失败"
    })
  }
})

//查询博客(最难最重要),用query就行
router.get("/search", async (req, res) => {
  /**
   * keyword 关键字
   * categoryId 分类编号
   * 
   * 分页:
   * page 页码
   * pageSize 分页大小
   * 
   */
  let { keyword, categoryId, page, pageSize } = req.query
  keyword = keyword == null ? "" : keyword
  categoryId = categoryId == null ? 0 : categoryId
  page = page == null ? 1 : page
  pageSize = pageSize == null ? 10 : pageSize

  let params = []
  let whereSqls = []
  let whereSqlStr = ""

  if (keyword !== null) {
    whereSqls.push(" (`title` like ? or `content` like ?) ")
    params.push("%" + keyword + "%")
    params.push("%" + keyword + "%")
  }
  if (categoryId !== 0) {
    whereSqls.push(" `category_id` = ? ")
    params.push(categoryId)
  }

  if (whereSqls.length > 0) {
    whereSqlStr = " where " + whereSqls.join(" and ")//只有一个时不会加上and
  }
  //[" `title` like ? or `content` like ?"," `category_id` = ? "]
  //whereSqlStr = where `title` like ? or `content` like ? and `category_id` = ?

  //查分页数据
  let searchSql = " select `id`,`category_id`,`create_time`,`title`,substr(`content`,0,50) as `content` from `blog` " + whereSqlStr + "order by `create_time` desc limit ?,? "
  let searchSqlParams = params.concat([(page - 1) * pageSize, pageSize])
  //1,10   2,10    3,10
  //0,10   10,10   20,10


  //查询数据总数
  let searchCount = " select count(*) from `blog` " + whereSqlStr
  let searchCountParams = params


  //分页数据
  let searchResult = await db.async.all(searchSql, searchSqlParams)
  let countResult = await db.async.all(searchCount, searchCountParams)

  // console.log(searchSql);检查SQL是否正确

  if (searchResult.err == null && countResult.err == null) {
    res.send({
      code: 200,
      msg: "(*^▽^*)查询成功",
      data: {
        keyword,
        categoryId,
        page,
        pageSize,
        rows: searchResult.rows,
        // count: countResult.rows[0].count  //这里我们发现count没给到客户端,打印的是{ err: null, rows: [ { 'count(*)': 1 } ] },此时有两种解决方法         1.searchCount改成let searchCount = " select count(*) as count from `blog` " + whereSqlStr
        count: countResult.rows[0]['count(*)'] //2.索引符（[]） 和 点操作符（.）的使用,详细见学习记录
      }
    })
  } else {
    res.send({
      code: 500,
      msg: "o(╥﹏╥)o查询失败"
    })
  }
})

//查询单篇博客
router.get("/detail", async (req, res) => {
  let { id } = req.query
  let detail_sql = " select * from `blog` where `id` = ? "
  let { err, rows } = await db.async.all(detail_sql, [id])
  if (err == null) {
    res.send({
      code: 200,
      msg: "获取成功",
      rows
    })
  } else {
    res.send({
      code: 500,
      msg: "获取失败"
    })
  }
})
module.exports = router